﻿Imports System.Linq
Imports System.Data
Imports System.Data.SqlClient

Public Class DAL

#Region "Login"

    Public Function ObterUser(ByVal nome As String, _
                              ByVal password As String) As IQueryable(Of Utilizadore)

        Try

            Dim baseDados As New UtilizadoresDataContext

            Dim user = From u In baseDados.Utilizadores _
                        Where u.UserLogin.ToLower = nome.ToLower And _
                        u.Password.ToLower = password.ToLower _
                        Select u

            Return user

        Catch ex As Exception

            Return Nothing

        End Try

    End Function

    Public Function base64Encode(ByVal data As String) As String

        Try

            Dim encData_byte As Byte() = New Byte(data.Length - 1) {}
            encData_byte = System.Text.Encoding.UTF8.GetBytes(data)
            Dim encodedData = Convert.ToBase64String(encData_byte)
            Return encodedData

        Catch ex As Exception
            Throw New Exception("Error in base64Encode" & ex.Message.ToString)
        End Try

    End Function

    Public Function base64Decode(ByVal data As String) As String

        Try
            Dim encoder As New System.Text.UTF8Encoding()
            Dim utf8Decode As System.Text.Decoder = encoder.GetDecoder

            Dim todecode_byte As Byte() = Convert.FromBase64String(data)
            Dim charcount As Integer = utf8Decode.GetCharCount(todecode_byte, 0, todecode_byte.Length)
            Dim decoded_char As Char() = New Char(charcount) {}
            utf8Decode.GetChars(todecode_byte, 0, todecode_byte.Length, decoded_char, 0)
            Dim result As String = New String(decoded_char)
            Return result

        Catch ex As Exception
            Throw New Exception("Error in base64Decode" & ex.Message.ToString)
        End Try
    End Function

#End Region

#Region "Preenchimento Dados"

    Public Function ObterRank() As IQueryable(Of Jogadore)

        Dim baseDados As New JogadoresDataContext

        Dim jogadores = From j In baseDados.Jogadores _
                         Select j Order By j.Pontos Descending

        Return jogadores

    End Function

    Public Function ObterRankSP() As IQueryable(Of ObterRankResult)

        Try

            Dim baseDados As New ObterRankSPDataContext

            Return baseDados.ObterRank.AsQueryable


        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function ObterRankPorUserSP(ByVal UserID As Integer) As IQueryable(Of ObterRankPorUserResult)

        Try

            Dim baseDados As New ObterRankPorUserSPDataContext

            Return baseDados.ObterRankPorUser(UserID).AsQueryable

        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function ObterJogos() As IQueryable(Of Jogo)

        Dim baseDados As New JogosDataContext

        Dim jogos = From j In baseDados.Jogos _
                     Select j Order By j.Data Descending

        Return jogos

    End Function

    Public Function ObterDatasJogos() As IQueryable(Of ObterDatasJogosResult)

        Dim baseDados As New ObterDatasJogosDataContext

        Dim jogos = From j In baseDados.ObterDatasJogos.AsQueryable

        Return jogos

    End Function

    Public Function ObterJogoPorID(ByVal IDJogo As Integer) As IQueryable(Of Jogo)

        Try
            Dim baseDados As New JogosDataContext

            Dim jogo = From j In baseDados.Jogos _
                       Where j.IDJogos = IDJogo _
                       Select j

            Return jogo

        Catch ex As Exception
            Return Nothing
        End Try

    End Function

    Public Function ObterListaJogadores(ByVal userID As Integer) As IQueryable(Of Jogadore)

        Try

            Dim baseDados As New JogadoresDataContext

            Dim jogadores = From j In baseDados.Jogadores _
                            Where j.UserID <> userID _
                            Select j Order By j.Pontos Ascending

            Return jogadores

        Catch ex As Exception

            Return Nothing

        End Try

    End Function

    Public Function ObterListaJogadoresSP(ByVal userID As Integer, _
                                          ByVal IDJogo As Integer) As IQueryable(Of ObterListaJogadoresResult)

        Try

            Dim baseDados As New ObterListaJogadoresSPDataContext

            Return baseDados.ObterListaJogadores(userID, IDJogo).AsQueryable

        Catch ex As Exception

            Return Nothing

        End Try

    End Function

    Public Function ObterListaJogadoresTrofSP(ByVal userID As Integer) As IQueryable(Of ObterListaJogadoresTrofResult)

        Try

            Dim baseDados As New ObterListaJogadoresTrofSPDataContext

            Return baseDados.ObterListaJogadoresTrof(userID).AsQueryable

        Catch ex As Exception

            Return Nothing

        End Try

    End Function

    Public Function ObterTrofeus() As IQueryable(Of Trofeus)

        Try

            Dim baseDados As New TrofeusDataContext

            Dim trofeus = From t In baseDados.Trofeus _
                          Select t Order By t.IDTrofeu Ascending

            Return trofeus

        Catch ex As Exception

            Return Nothing

        End Try

    End Function

    Public Function ObterTrofeuPorID(ByVal IDTrofeu As Integer) As IQueryable(Of Trofeus)

        Try
            Dim baseDados As New TrofeusDataContext

            Dim trofeu = From t In baseDados.Trofeus _
                          Where t.IDTrofeu = IDTrofeu _
                          Select t

            Return trofeu

        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function ObterConstEquipas() As IQueryable(Of ConstituicaoEquipa)

        Try

            Dim baseDados As New ConstEquipasDataContext

            Dim constEq = From c In baseDados.ConstituicaoEquipas _
                          Select c

            Return constEq

        Catch ex As Exception

            Return Nothing

        End Try

    End Function

    Public Function ObterConstEquipasPorIDEquipaEIDJogo(ByVal IDJogo As Integer, _
                                                        ByVal IDEquipa As Integer) _
                                                        As IQueryable(Of ObterConstEquipasPorIDJogoeIDEquipaResult)

        Try
            Dim baseDados As New ObterConstEquipasPorJogoeEquipaDataContext

            Return baseDados.ObterConstEquipasPorIDJogoeIDEquipa(IDJogo, IDEquipa).AsQueryable

        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function ObterConstEquipasIDJogo(ByVal IDJogo As Integer) _
    As IQueryable(Of ObterConstEquipasPorIDJogoResult)

        Try
            Dim baseDados As New ObterConstEquipasPorJogoDataContext

            Return baseDados.ObterConstEquipasPorIDJogo(IDJogo).AsQueryable

        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function ObterFlagVotou(ByVal userID As Integer, _
                                   ByVal IDTrofeu As Integer) As IQueryable(Of Votacao)
        Try

            Dim baseDados As New VotacaoDataContext

            Dim vot = From v In baseDados.Votacaos _
                      Where v.IDJogador = userID And v.IDTrofeu = IDTrofeu _
                      Select v

            Return vot

        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function ObterFlagVotouMVP(ByVal userID As Integer, _
                                      ByVal IDJogo As Integer) As IQueryable(Of VotacaoMVP)

        Try

            Dim baseDados As New VotacaoMVPDataContext

            Dim voto = From v In baseDados.VotacaoMVPs _
                       Where v.IDJogador = userID _
                       And v.IDJogo = IDJogo _
                       Select v

            Return voto

        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function ObterUserIDPorNome(ByVal nome As String) As IQueryable(Of Utilizadore)

        Try
            Dim baseDados As New UtilizadoresDataContext

            Dim user = From u In baseDados.Utilizadores _
                       Where u.NomeUtilizador.ToLower = nome.ToLower _
                       Select u

            Return user

        Catch ex As Exception

            Return Nothing

        End Try

    End Function

    Public Function ObterTodosUsers() As IQueryable(Of Utilizadore)

        Try

            Dim baseDados As New UtilizadoresDataContext

            Dim users = From u In baseDados.Utilizadores _
                        Select u Order By u.IDUser Ascending

            Return users

        Catch ex As Exception
            Return Nothing
        End Try

    End Function

    Public Function ObterDadosUserPorID(ByVal userID As Integer) As IQueryable(Of Utilizadore)

        Try
            Dim baseDados As New UtilizadoresDataContext

            Dim user = From u In baseDados.Utilizadores _
                       Where u.IDUser = userID _
                       Select u

            Return user

        Catch ex As Exception

            Return Nothing

        End Try

    End Function

    Public Function ObterVotacaoTrofeus() As IQueryable(Of Votacao)

        Try

            Dim baseDados As New VotacaoDataContext

            Dim votacao = From v In baseDados.Votacaos _
                          Select v Order By v.NumVotos Descending

            Return votacao

        Catch ex As Exception
            Return Nothing
        End Try

    End Function

    Public Function ObterVotacaoTrofeusSP() As IQueryable(Of ObterVotacaoTrofeusSPResult)

        Try
            Dim baseDados As New VotacaoTrofeusSPDataContext

            Return baseDados.ObterVotacaoTrofeusSP.AsQueryable

        Catch ex As Exception
            Return Nothing
        End Try

    End Function

    Public Function ObterVotacaoMVP() As IQueryable(Of Votacao)

        Try

            Dim baseDados As New VotacaoMVPDataContext

            Dim votacao = From v In baseDados.VotacaoMVPs _
                          Select v Order By v.NumVotos Descending

            Return votacao

        Catch ex As Exception
            Return Nothing
        End Try

    End Function

    Public Function ObterVotacaoMVPSP() As IQueryable(Of ObterVotacaoMVPSPResult)

        Try

            Dim baseDados As New VotacaoMVPSPDataContext

            Return baseDados.ObterVotacaoMVPSP.AsQueryable

        Catch ex As Exception
            Return Nothing
        End Try

    End Function

    Public Function ObterEquipas() As IQueryable(Of Equipa)

        Try
            Dim baseDados As New EquipasDataContext

            Dim equipa = From e In baseDados.Equipas _
                         Select e Order By e.IDEquipa Ascending

            Return equipa

        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function ObterEquipaPorID(ByVal IDEquipa As Integer) As IQueryable(Of Equipa)

        Try
            Dim baseDados As New EquipasDataContext

            Dim equipa = From e In baseDados.Equipas _
                         Where e.IDEquipa = IDEquipa _
                         Select e

            Return equipa

        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function ObterMails() As IQueryable(Of Utilizadore)

        Try
            Dim baseDados As New UtilizadoresDataContext

            Dim mail = From m In baseDados.Utilizadores _
                       Select m Order By m.IDUser Ascending

            Return mail

        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function ObterEmailsUltimoJogo() As IQueryable(Of ObterEmailsUltimoJogoResult)

        Try
            Dim baseDados As New ObterEmailsUltimoJogoDataContext

            Return baseDados.ObterEmailsUltimoJogo().AsQueryable

        Catch ex As Exception

            Return Nothing
        End Try
    End Function

    Public Function ObterDadosJogador(ByVal IDUtilizador As Integer) As IQueryable(Of ObterDadosJogadorResult)

        Try
            Dim baseDados As New ObterDadosJogadorDataContext

            Return baseDados.ObterDadosJogador(IDUtilizador).AsQueryable

        Catch ex As Exception

            Return Nothing
        End Try

    End Function

#End Region

#Region "Estatisticas"

    Public Function ObterMaisVitorias()
        Try
            Dim bd As New ObterEstatisticasDataContext

            Dim ordem = (From o In bd.ObterEstatisticas _
                        Select o.Vitorias, o.NomeUtilizador _
                        Order By Vitorias Descending).Take(3)

            Return ordem

        Catch ex As Exception

            Return Nothing
        End Try
    End Function

    Public Function ObterMenosVitorias()

        Try
            Dim bd As New ObterEstatisticasDataContext

            Dim ordem = (From o In bd.ObterEstatisticas
                        Select o.Vitorias, o.NomeUtilizador
                        Order By Vitorias Ascending).Take(3)

            Return ordem

        Catch ex As Exception

            Return Nothing
        End Try
    End Function

    Public Function ObterMaisEmpates()
        Try
            Dim bd As New ObterEstatisticasDataContext

            Dim ordem = (From o In bd.ObterEstatisticas _
                        Select o.Empates, o.NomeUtilizador _
                        Order By Empates Descending).Take(3)

            Return ordem

        Catch ex As Exception

            Return Nothing
        End Try
    End Function

    Public Function ObterMenosEmpates()

        Try
            Dim bd As New ObterEstatisticasDataContext

            Dim ordem = (From o In bd.ObterEstatisticas
                        Select o.Empates, o.NomeUtilizador
                        Order By Empates Ascending).Take(3)

            Return ordem

        Catch ex As Exception

            Return Nothing
        End Try
    End Function

    Public Function ObterMaisDerrotas()
        Try
            Dim bd As New ObterEstatisticasDataContext

            Dim ordem = (From o In bd.ObterEstatisticas _
                        Select o.Derrotas, o.NomeUtilizador _
                        Order By Derrotas Descending).Take(3)

            Return ordem

        Catch ex As Exception

            Return Nothing
        End Try
    End Function

    Public Function ObterMenosDerrotas()

        Try
            Dim bd As New ObterEstatisticasDataContext

            Dim ordem = (From o In bd.ObterEstatisticas
                        Select o.Derrotas, o.NomeUtilizador
                        Order By Derrotas Ascending).Take(3)

            Return ordem

        Catch ex As Exception

            Return Nothing
        End Try
    End Function

#End Region

#Region "Operações Front - End"

    Public Sub InsereLogErros(ByVal metodo As String, _
                                   ByVal mensagem As String)

        Try

            Dim baseDados As New LogErrosDataContext

            Dim logErro As New LogErro

            With logErro
                .Metodo = metodo
                .MsgErro = mensagem
                .Data = Date.Now
            End With

            baseDados.LogErros.InsertOnSubmit(logErro)
            baseDados.SubmitChanges()

        Catch ex As Exception

        End Try

    End Sub

    Public Function InserirVoto(ByVal userID As Integer, _
                                ByVal IDTrofeu As Integer, _
                                ByVal numVotos As Integer, _
                                ByVal votou As Boolean) As Boolean

        Try

            Dim baseDados As New VotacaoDataContext

            Dim voto As New Votacao

            With voto
                .IDJogador = userID
                .IDTrofeu = IDTrofeu
                .NumVotos = numVotos
                .Votou = votou
            End With

            baseDados.Votacaos.InsertOnSubmit(voto)
            baseDados.SubmitChanges()
            Return True

        Catch ex As Exception

            Return False

        End Try

    End Function

    Public Function ActualizaVoto(ByVal userID As Integer, _
                                  ByVal IDTrofeu As Integer) As Boolean

        Try
            Dim baseDados As New VotacaoDataContext

            Dim voto = (From v In baseDados.Votacaos _
                       Where v.IDJogador = userID _
                       And v.IDTrofeu = IDTrofeu _
                       Select v).FirstOrDefault

            With voto
                .NumVotos = voto.NumVotos + 1
            End With

            baseDados.SubmitChanges()
            Return True

        Catch ex As Exception

            Return False

        End Try

    End Function

    Public Function MarcarVotou(ByVal userIDQueVotou As Integer, _
                                ByVal IDTrofeu As Integer, _
                                ByVal votou As Boolean) As Boolean

        Try

            Dim baseDados As New VotacaoDataContext

            Dim vota = (From v In baseDados.Votacaos _
                        Where v.IDJogador = userIDQueVotou _
                        And v.IDTrofeu = IDTrofeu _
                        Select v).FirstOrDefault

            With vota
                .Votou = votou
            End With

            baseDados.SubmitChanges()

            Return True

        Catch ex As Exception

            Return False

        End Try

    End Function

    Public Function InsereVotoMVP(ByVal userID As Integer, _
                                  ByVal IDJogo As Integer, _
                                  ByVal numVotos As Integer, _
                                  ByVal votou As Boolean) As Boolean

        Try

            Dim baseDados As New VotacaoMVPDataContext

            Dim voto As New VotacaoMVP

            With voto
                .IDJogador = userID
                .IDJogo = IDJogo
                .NumVotos = numVotos
                .Votou = votou
            End With

            baseDados.VotacaoMVPs.InsertOnSubmit(voto)
            baseDados.SubmitChanges()
            Return True

        Catch ex As Exception

            Return False

        End Try

    End Function

    Public Function ActualizaVotoMVP(ByVal userID As Integer, _
                                     ByVal IDJogo As Integer) As Boolean

        Try

            Dim baseDados As New VotacaoMVPDataContext

            Dim voto = (From v In baseDados.VotacaoMVPs _
                        Where v.IDJogador = userID _
                        And v.IDJogo = IDJogo _
                        Select v).FirstOrDefault

            With voto
                .NumVotos = voto.NumVotos + 1
            End With

            baseDados.SubmitChanges()
            Return True

        Catch ex As Exception

            Return False

        End Try

    End Function

    Public Function MarcarVotouMVP(ByVal userIDQueVotou As Integer, _
                                   ByVal IDJogo As Integer, _
                                   ByVal votou As Boolean) As Boolean

        Try
            Dim baseDados As New VotacaoMVPDataContext

            Dim voto = (From v In baseDados.VotacaoMVPs _
                        Where v.IDJogador = userIDQueVotou _
                        And v.IDJogo = IDJogo _
                        Select v).FirstOrDefault

            With voto
                .Votou = votou
            End With

            baseDados.SubmitChanges()
            Return True

        Catch ex As Exception

            Return False

        End Try

    End Function

    Public Function VerificaParticipacao(ByVal IDUser As Integer, _
                                         ByVal IDJogos As Integer) As Boolean

        Try
            Dim baseDados As New VerificaParticipacaoDataContext

            Return baseDados.VerificaParticipacao(IDUser, IDJogos).First.Participou

        Catch ex As Exception

            Return False
        End Try
    End Function
#End Region

#Region "Operações Back - Office"

    Public Function AlterarDadosUser(ByVal userID As Integer, _
                                     ByVal userLogin As String, _
                                     ByVal nome As String, _
                                     ByVal password As String) As Boolean

        Try
            Dim baseDados As New UtilizadoresDataContext

            Dim user = (From u In baseDados.Utilizadores _
                       Where u.IDUser = userID _
                       Select u).FirstOrDefault

            With user
                .UserLogin = userLogin
                .NomeUtilizador = nome
                .Password = password
            End With

            baseDados.SubmitChanges()
            Return True

        Catch ex As Exception

            Return False

        End Try

    End Function

    Public Function ApagarUtilizador(ByVal userID As Integer) As Boolean

        Try
            Dim baseDados As New UtilizadoresDataContext
            Dim jogadores As New JogadoresDataContext

            Dim user = (From u In baseDados.Utilizadores _
                       Where u.IDUser = userID _
                       Select u).FirstOrDefault

            Dim joga = (From j In jogadores.Jogadores _
                        Where j.UserID = userID _
                        Select j).FirstOrDefault

            baseDados.Utilizadores.DeleteOnSubmit(user)
            baseDados.SubmitChanges()

            jogadores.Jogadores.DeleteOnSubmit(joga)
            jogadores.SubmitChanges()

            Return True

        Catch ex As Exception

            Return False

        End Try

    End Function

    Public Function InserirUtilizador(ByVal userLogin As String, _
                                      ByVal nome As String, _
                                      ByVal password As String) As Boolean

        Try
            Dim baseDados As New UtilizadoresDataContext

            Dim user As New Utilizadore

            With user
                .UserLogin = userLogin
                .NomeUtilizador = nome
                .Password = password
            End With

            baseDados.Utilizadores.InsertOnSubmit(user)
            baseDados.SubmitChanges()
            Return True

        Catch ex As Exception

            Return False

        End Try

    End Function

    Public Function InserirJogador(ByVal userID As Integer) As Boolean

        Try
            Dim baseDados As New JogadoresDataContext

            Dim jogador As New Jogadore

            With jogador
                .Derrotas = 0
                .Empates = 0
                .Golos_Marcados = 0
                .Golos_Sofridos = 0
                .MVP = 0
                .Pontos = 0
                .Vitorias = 0
                .UserID = userID
            End With

            baseDados.Jogadores.InsertOnSubmit(jogador)
            baseDados.SubmitChanges()
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function InserirJogo(ByVal data As Date, _
                                ByVal resultado As String, _
                                ByVal golosA As Integer, _
                                ByVal golosB As Integer, _
                                ByVal mvp As String) As Boolean

        Try
            Dim baseDados As New JogosDataContext

            Dim jogo As New Jogo

            With jogo
                .Data = data
                .Resultado = resultado
                .GolosEA = golosA
                .GolosEB = golosB
                .MVP = mvp
            End With

            baseDados.Jogos.InsertOnSubmit(jogo)
            baseDados.SubmitChanges()
            Return True

        Catch ex As Exception
            Return False
        End Try

    End Function

    Public Function AlterarJogo(ByVal IDJogo As Integer, _
                                ByVal data As Date, _
                                ByVal resultado As String, _
                                ByVal golosA As Integer, _
                                ByVal golosB As Integer, _
                                ByVal mvp As String) As Boolean
        Try
            Dim baseDados As New JogosDataContext

            Dim jogo = (From j In baseDados.Jogos _
                        Where j.IDJogos = IDJogo _
                        Select j).FirstOrDefault

            With jogo
                .Data = data
                .Resultado = resultado
                .GolosEA = golosA
                .GolosEB = golosB
                .MVP = mvp
            End With

            baseDados.SubmitChanges()
            Return True

        Catch ex As Exception
            Return False
        End Try

    End Function

    Public Function AlterarJogoActualizado(ByVal IDJogo As Integer, _
                                   ByVal actualizado As Boolean) As Boolean

        Try
            Dim baseDados As New JogosDataContext

            Dim jogo = (From j In baseDados.Jogos _
                       Where j.IDJogos = IDJogo _
                       Select j).FirstOrDefault

            With jogo
                .Actualizado = actualizado
            End With

            baseDados.SubmitChanges()
            Return True

        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function ApagarJogo(ByVal IDJogo As Integer) As Boolean
        Try
            Dim baseDados As New JogosDataContext

            Dim jogo = (From u In baseDados.Jogos _
                       Where u.IDJogos = IDJogo _
                       Select u).FirstOrDefault

            baseDados.Jogos.DeleteOnSubmit(jogo)
            baseDados.SubmitChanges()
            Return True

        Catch ex As Exception

            Return False

        End Try
    End Function

    Public Function InserirTrofeu(ByVal nomeTrofeu As String, _
                                  ByVal vencedor As String) As Boolean

        Try
            Dim baseDados As New TrofeusDataContext

            Dim trofeu As New Trofeus

            With trofeu
                .NomeTrofeu = nomeTrofeu
                .Vencedor = vencedor
            End With

            baseDados.Trofeus.InsertOnSubmit(trofeu)
            baseDados.SubmitChanges()
            Return True

        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function AlterarTrofeu(ByVal IDTrofeu As Integer, _
                                  ByVal nomeTrofeu As String, _
                                  ByVal vencedor As String) As Boolean

        Try
            Dim baseDados As New TrofeusDataContext

            Dim trofeu = (From t In baseDados.Trofeus _
                          Where t.IDTrofeu = IDTrofeu _
                          Select t).FirstOrDefault

            With trofeu
                .NomeTrofeu = nomeTrofeu
                .Vencedor = vencedor
            End With

            baseDados.SubmitChanges()
            Return True

        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function ApagarTrofeu(ByVal IDTrofeu As Integer) As Boolean
        Try
            Dim baseDados As New TrofeusDataContext

            Dim trofeu = (From u In baseDados.Trofeus _
                       Where u.IDTrofeu = IDTrofeu _
                       Select u).FirstOrDefault

            baseDados.Trofeus.DeleteOnSubmit(trofeu)
            baseDados.SubmitChanges()
            Return True

        Catch ex As Exception

            Return False

        End Try
    End Function

    Public Function InserirEquipa(ByVal nome As String, _
                                  ByVal golosMarcados As Integer, _
                                  ByVal golosSofridos As Integer, _
                                  ByVal vitorias As Integer, _
                                  ByVal empates As Integer, _
                                  ByVal derrotas As Integer) As Boolean

        Try
            Dim baseDados As New EquipasDataContext

            Dim equipa As New Equipa

            With equipa
                .NomeEquipa = nome
                .GolosMarcados = golosMarcados
                .GolosSofridos = golosSofridos
                .Vitorias = vitorias
                .Empates = empates
                .Derrotas = derrotas
            End With

            baseDados.Equipas.InsertOnSubmit(equipa)
            baseDados.SubmitChanges()
            Return True

        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function AlterarEquipa(ByVal IDEquipa As Integer, _
                                  ByVal nome As String, _
                                  ByVal golosMarcados As Integer, _
                                  ByVal golosSofridos As Integer, _
                                  ByVal vitorias As Integer, _
                                  ByVal empates As Integer, _
                                  ByVal derrotas As Integer) As Boolean

        Try
            Dim baseDados As New EquipasDataContext

            Dim equipa = (From e In baseDados.Equipas _
                         Where e.IDEquipa = IDEquipa _
                         Select e).FirstOrDefault

            With equipa
                .NomeEquipa = nome
                .GolosMarcados = golosMarcados
                .GolosSofridos = golosSofridos
                .Vitorias = vitorias
                .Empates = empates
                .Derrotas = derrotas
            End With

            baseDados.SubmitChanges()
            Return True

        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function ApagarEquipa(ByVal IDEquipa As Integer) As Boolean

        Try
            Dim baseDados As New EquipasDataContext

            Dim equipa = (From e In baseDados.Equipas _
                         Where e.IDEquipa = IDEquipa _
                         Select e).FirstOrDefault

            baseDados.Equipas.DeleteOnSubmit(equipa)
            baseDados.SubmitChanges()
            Return True

        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function InserirConstEquipa(ByVal UserID As Integer, _
                                       ByVal IDEquipa As Integer, _
                                       ByVal IDJogo As Integer) As Boolean

        Try
            Dim baseDados As New ConstEquipasDataContext

            Dim constEquipa As New ConstituicaoEquipa

            With constEquipa
                .IDJogador = UserID
                .IDEquipa = IDEquipa
                .IDJogos = IDJogo
            End With

            baseDados.ConstituicaoEquipas.InsertOnSubmit(constEquipa)
            baseDados.SubmitChanges()
            Return True

        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function ApagarConstEquipas(ByVal UserID As Integer, _
                                       ByVal IDJogo As Integer) As Integer

        Try
            Dim baseDados As New ConstEquipasDataContext

            Dim constEquipa = (From c In baseDados.ConstituicaoEquipas _
                              Where c.IDJogador = UserID _
                              And c.IDJogos = IDJogo _
                              Select c).FirstOrDefault

            baseDados.ConstituicaoEquipas.DeleteOnSubmit(constEquipa)
            baseDados.SubmitChanges()
            Return True

        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function ActualizarRank(ByVal UserID As Integer, _
                                   ByVal pontos As Integer, _
                                   ByVal golosMarcados As Integer, _
                                   ByVal golosSofridos As Integer, _
                                   ByVal vitorias As Integer, _
                                   ByVal empates As Integer, _
                                   ByVal derrotas As Integer) As Boolean

        Try

            Dim baseDados As New JogadoresDataContext

            Dim rank = (From r In baseDados.Jogadores _
                       Where r.UserID = UserID).FirstOrDefault

            With rank
                .Pontos = .Pontos + pontos
                .Golos_Marcados = .Golos_Marcados + golosMarcados
                .Golos_Sofridos = .Golos_Sofridos + golosSofridos
                .Vitorias = .Vitorias + vitorias
                .Empates = .Empates + empates
                .Derrotas = .Derrotas + derrotas
            End With

            baseDados.SubmitChanges()
            Return True

        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function ActualizarMVP(ByVal userID As Integer) As Boolean

        Try
            Dim baseDados As New JogadoresDataContext

            Dim joga = (From j In baseDados.Jogadores _
                       Where j.UserID = userID _
                       Select j).FirstOrDefault

            With joga
                .MVP = .MVP + 1
                .Pontos = .Pontos + 1
            End With

            baseDados.SubmitChanges()
            Return True

        Catch ex As Exception
            Return False
        End Try
    End Function

#End Region

End Class